

/**
	AUTHOR		: GANDHI
	DATE		: 03 MARCH, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET ALL FORECAST DETAILS BASED ON SEARCH CRITERIA. 
*/




ALTER                        PROC SP_FORECAST_REPORT(
 
 @CUSTOMER_INPUT VARCHAR(100),
 @PROJECT_INPUT VARCHAR(100),
 @PRODUCT_INPUT VARCHAR(100),
 @ODM_INPUT VARCHAR(100),
 @MONTH_FROM INT,
 @MONTH_TO INT,
 @YEAR INT, 
 @REPORT_ON VARCHAR(50),
 @defaultPrompt varchar(20))
 
 -- @MONTH_FROM CONTAINS START MONTH OR START QUARTER DEPENDING UP ON FORECAST SETUP
 -- @MONTH_TO CONTAINS NEXT NUMBER OF MONTHS OR QUARTERS OR YEARS REPORT TO BE SHOWN
 
 AS
 BEGIN
	
	
	DECLARE @RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY INT, PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50))
	DECLARE @SPERSON_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY INT, PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50))
   	DECLARE @TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), YEAR INT, QUANTITY INT, PRICE FLOAT , CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50) )
	DECLARE @TOTAL_RESULTS TABLE( CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY INT, PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50) )	
	DECLARE @BUILD_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY INT, PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50))

	DECLARE @BYPERIOD VARCHAR(50)
	SELECT @BYPERIOD = TFNAME FROM FCTFRAME WHERE TFSELECT = 0
	
	SET @MONTH_TO = 3     -- SHOW NEXT RESTRICTED TO 3 MONTHS FOR STATIC REPORTS

	DECLARE @TO_MONTH INT	
	DECLARE @YEAR_TO INT
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
	BEGIN

		IF RTRIM(@MONTH_FROM) = -1
	 	BEGIN
	 		SET @MONTH_FROM = 0
	 	END
	 
	 	IF RTRIM(@MONTH_TO) = -1
	 	BEGIN
	 		SET @MONTH_TO = 11
	 	END
	 	
	 	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 
	 
	 	SET @YEAR_TO = @YEAR + @TO_MONTH/12 
	 	SET @MONTH_TO = @TO_MONTH%12	
	END
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
	BEGIN 		
	
		IF RTRIM(@MONTH_FROM) = -1
	 	BEGIN
	 		SET @MONTH_FROM = 0
	 	END
	 
	 	IF RTRIM(@MONTH_TO) = -1
	 	BEGIN
	 		SET @MONTH_TO = 3
	 	END
	 	
	 	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 
	 
		SET @YEAR_TO = @YEAR + @TO_MONTH/4 
 		SET @MONTH_TO = @TO_MONTH%4	
	END
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
	BEGIN 		
		SET @YEAR_TO = @YEAR + @MONTH_TO
	END
	
	DECLARE @SF_SPID INT
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
 	DECLARE @SF_USERLIST1 VARCHAR(100)
	DECLARE @SF_USERDATE1 VARCHAR(100)
	DECLARE @SF_USERTXT1 VARCHAR(100)
	DECLARE @SF_USERTXT2 VARCHAR(100)
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	DECLARE @SPERSON_SPID INT
	DECLARE @FC_MONTH VARCHAR(50)	
	DECLARE @FC_QUARTER VARCHAR(50)
	DECLARE @SF_FORECAST_TYPE VARCHAR(50)
	DECLARE @SF_BUILDNO VARCHAR(50)

 	DECLARE @SPERSON_CURSOR CURSOR 
	

	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	 	
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, ''SALES'' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_CUSTOMER_INPUT VARCHAR(100), @PRM_PROJECT_INPUT VARCHAR(100), @PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @MONTH_FROM INT, @MONTH_TO INT, @TO_MONTH INT, @YEAR INT, @YEAR_TO INT '
		
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @PROJECT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PROJID = @PRM_PROJECT_INPUT '
		END	
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( MONTH >= @MONTH_FROM AND YEAR = @YEAR AND MONTH < @TO_MONTH )	OR
 		      		( MONTH < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			      		( YEAR < @YEAR_TO AND YEAR > @YEAR ) ) '
		
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( QUARTER >= @MONTH_FROM AND YEAR = @YEAR AND QUARTER < @TO_MONTH )	OR
 		      		( QUARTER < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			     		 ( YEAR < @YEAR_TO AND YEAR > @YEAR ) )	'
		END
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( YEAR >= @YEAR AND YEAR < @YEAR_TO ) '
		END		

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_PROJECT_INPUT = @PROJECT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @MONTH_FROM = @MONTH_FROM, @MONTH_TO = @MONTH_TO, @TO_MONTH = @TO_MONTH, @YEAR = @YEAR, @YEAR_TO = @YEAR_TO
	END
	ELSE 
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT BUILDNO, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, 0, ''BUILD'' FROM FCBUILDMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_CUSTOMER_INPUT VARCHAR(100), @PRM_PROJECT_INPUT VARCHAR(100), @PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @MONTH_FROM INT, @MONTH_TO INT, @TO_MONTH INT, @YEAR INT, @YEAR_TO INT '
		
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @PROJECT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PROJID = @PRM_PROJECT_INPUT '
		END	
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( MONTH >= @MONTH_FROM AND YEAR = @YEAR AND MONTH < @TO_MONTH )	OR
 		      		( MONTH < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			      		( YEAR < @YEAR_TO AND YEAR > @YEAR ) ) '
		
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( QUARTER >= @MONTH_FROM AND YEAR = @YEAR AND QUARTER < @TO_MONTH )	OR
 		      		( QUARTER < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			     		 ( YEAR < @YEAR_TO AND YEAR > @YEAR ) )	'
		END
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( YEAR >= @YEAR AND YEAR < @YEAR_TO ) '
		END		

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_PROJECT_INPUT = @PROJECT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @MONTH_FROM = @MONTH_FROM, @MONTH_TO = @MONTH_TO, @TO_MONTH = @TO_MONTH, @YEAR = @YEAR, @YEAR_TO = @YEAR_TO
	END


		
 	IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN 
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN  
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) 
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
	 	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT ))*/
		END
	END	
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ODMID = @ODM_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END

 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER)
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER))*/
		END
	END
 
	
		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
 				
				INSERT INTO @RESULTS VALUES( @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_FORECAST_TYPE )
 					
				SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
				UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 					
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	
 		 																
 		CLOSE FORECASTCURSOR
 		DEALLOCATE FORECASTCURSOR 

		
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
			OPEN @SPERSON_CURSOR 
	 			FETCH NEXT FROM @SPERSON_CURSOR
	 			INTO @SPERSON_SPID
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					INSERT INTO @SPERSON_RESULTS SELECT * FROM @RESULTS WHERE SPERSON_ID = @SPERSON_SPID
					 										
	 				FETCH NEXT FROM @SPERSON_CURSOR
	 				INTO @SPERSON_SPID
	 			END	
	 				DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT * FROM @SPERSON_RESULTS
					IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
					BEGIN
						INSERT INTO @TEMP_RESULTS SELECT SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, YEAR, SUM(QUANTITY) FC_QUANTITY, SUM(PRICE) FC_PRICE, CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @RESULTS
						GROUP BY SPERSON_ID, CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, CONFIDENCE_LEVEL, FORECAST_TYPE, YEAR
						DELETE FROM @RESULTS
						INSERT INTO @RESULTS SELECT NULL, SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, NULL, NULL, YEAR, QUANTITY, PRICE, NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @TEMP_RESULTS	
					END	
					INSERT INTO @TOTAL_RESULTS SELECT  CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, SUM(QUANTITY) QUANTITY, SUM(PRICE) PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @RESULTS				
					GROUP BY CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, FORECAST_TYPE, MONTH, QUARTER, YEAR
					DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT NULL, NULL, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, QUANTITY, PRICE,NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @TOTAL_RESULTS												
	 																
	 		CLOSE @SPERSON_CURSOR
	 		DEALLOCATE @SPERSON_CURSOR 
			
		END
		ELSE
		BEGIN
			
			DECLARE @BUILD_CURSOR CURSOR
			DECLARE @BUILD_NO VARCHAR(50)

			SET @BUILD_CURSOR = CURSOR FOR
			SELECT DISTINCT 'BFC-'+CONVERT(VARCHAR,(MAX(CONVERT(INT,SUBSTRING(BUILDNO, 5, LEN(BUILDNO)))))) BUILDNO FROM @RESULTS GROUP BY CUSTOMER,PROJECT,PRODUCT,ODM,MONTH,QUARTER,YEAR
			
			OPEN @BUILD_CURSOR 
	 			FETCH NEXT FROM @BUILD_CURSOR
	 			INTO @BUILD_NO

	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN	
					INSERT INTO @BUILD_RESULTS SELECT * FROM @RESULTS WHERE BUILDNO = @BUILD_NO AND SPERSON_ID IS NULL	
					
					FETCH NEXT FROM @BUILD_CURSOR
					INTO @BUILD_NO

				END
					DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT * FROM @BUILD_RESULTS
			
			CLOSE @BUILD_CURSOR
			DEALLOCATE @BUILD_CURSOR

		END
 		
	SELECT * FROM @RESULTS	
 
 END
 
 



